from connsqlite.connect_sqlite3 import Sqlite3Database

"""
 bin 详细信息
"""


class RapidBinManager:
    def __init__(self):
        self.db_name = "rapid.db"
        self.table_name = "rapid_bin_info"
        self.columns = "id INTEGER PRIMARY KEY, " \
                       "number TEXT(100), " \
                       "brand TEXT(100), " \
                       "scheme TEXT(100), " \
                       "type TEXT(100), " \
                       "level TEXT(100), " \
                       "is_commercial TEXT(100), " \
                       "is_prepaid TEXT(100), " \
                       "currency TEXT(100), " \
                       "issuer_name TEXT(100), " \
                       "issuer_website TEXT(100), " \
                       "issuer_phone TEXT(100), " \
                       "country_name TEXT(100), " \
                       "country_native TEXT(100), " \
                       "country_flag TEXT(100), " \
                       "country_numeric TEXT(100), " \
                       "country_capital TEXT(100), " \
                       "country_currency TEXT(100), " \
                       "country_currency_name TEXT(100), " \
                       "country_currency_symbol TEXT(100), " \
                       "country_region TEXT(100), " \
                       "country_subregion TEXT(100), " \
                       "country_idd TEXT(100), " \
                       "country_alpha2 TEXT(100), " \
                       "country_alpha3 TEXT(100), " \
                       "country_language TEXT(100), " \
                       "country_language_code TEXT(100), " \
                       "create_time TEXT(100) DEFAULT CURRENT_TIMESTAMP, " \
                       "update_time TEXT(100) DEFAULT CURRENT_TIMESTAMP, " \
                       "CONSTRAINT unique_bin UNIQUE (number)"
        self.sqlite3 = Sqlite3Database(self.db_name)
        self.sqlite3.create_table(self.table_name, self.columns)

    def selectDataByPager(self, offset, pageSize, query_columns=None):
        """
        分页查询列表
        :param offset: 分页
        :param pageSize: 分页数
        :param query_columns: 需要查询列
        :return:
        """
        limit = f" LIMIT {pageSize} OFFSET {offset} "
        return self.sqlite3.select_data_by_limit(self.table_name, columns=query_columns, limit=limit)
        # new_list_of_tuples = []
        # if sql_data is not None:
        #     for t in sql_data:
        #         ts = (t[1], t[2], t[4], t[5], [8], t[9], t[12], t[14], t[17], t[23], t[24], t[28])
        #         new_list_of_tuples.append(ts)
        # return new_list_of_tuples

    def insertBindata(self, data: dict):
        """
        字典插入
        :param data:
        :return:
        """
        with self.sqlite3.conn:  # 添加事务
            self.sqlite3.insert_data(self.table_name, data)

    def insertBinByTuple(self, data: tuple, headers=None):
        """
        元组插入
        :param headers:
        :param data:
        :return:
        """
        with self.sqlite3.conn:
            self.sqlite3.insert_data_tuple(self.table_name, data, headers)

    def batchInsertBinByTuple(self, data, headers=None):
        """
        元组批量插入
        :param headers:
        :param data: 需要保存的元组数据
        :return:
        """
        with self.sqlite3.conn:
            self.sqlite3.batch_insert_data_by_tuple(self.table_name, data, headers)

    def selectInfoByBIN(self, BIN, query_columns=None):
        """
        根据BIN查询
        :return:
        """
        condition = f" number = '{BIN}' "
        return self.sqlite3.select_data(self.table_name, columns=query_columns, condition=condition)

    def selectBinByBins(self, bins, query_columns=None):
        """
        根据bins查询
        :return:
        """
        binList = tuple(bins)
        if len(binList) == 1:
            condition = f" number = '{binList[0]}' "
        else:
            condition = f" number in {binList} "
        return self.sqlite3.select_data(self.table_name, columns=query_columns, condition=condition)

    def batchDelByBins(self, bins):
        """
         批量删除BINS
        :param bins: bin的集合
        :return:
        """
        if bins is None or len(bins) == 0:
            return
        binList = tuple(bins)
        if len(binList) == 1:
            condition = f" number = '{binList[0]}' "
        else:
            condition = f" number in {binList} "
        with self.sqlite3.conn:  # 添加事务
            self.sqlite3.delete_data(table_name=self.table_name, condition=condition)

    def selectExportData(self, startTime, endTime):
        """
        导出数据
        :param startTime: 开始时间
        :param endTime: 结束时间
        :return:
        """
        conditon = None
        if startTime is not None and endTime is not None:
            conditon = f" update_time >= '{startTime}' and update_time <= '{endTime}' "
        return self.sqlite3.select_data_by_limit(self.table_name, condition=conditon)